/**
 * @FileName: ExcelLargeDataUtils.java
 * @Author
 * @Description:
 * @Date 2016年5月24日 下午3:26:42
 * @CopyRight ZTE Corporation
 */
package cn.hpclub.server.util;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.jfinal.kit.Prop;
import com.jfinal.kit.PropKit;
import com.jfinal.plugin.activerecord.Record;

import cn.hpclub.server.bean.SystemConfig;
import cn.hpclub.server.util.excel.ExportUtils;

public class ExcelLargeDataUtils{
    private static Logger logger = LoggerFactory.getLogger(ExcelLargeDataUtils.class);

    /**
     * @Title: queryAndExport
     * @Description:查询并导出数据
     * @param headers:导出文件列标题
     * @param columns:导出数据对应库字段
     * @param sql:导出SQL查询语句
     * @param exportFileName:导出文件名
     * @param exportFilePath:导出文件保存路径
     * @throws IOException
     */
    public static String exportData(final String[] headers, final String[] columns, String sql, String exportFileName,
            String exportFilePath) throws IOException{
        long startAll = System.currentTimeMillis();
        logger.debug("exportFilePath=" + exportFilePath + ",exportFileName=" + exportFileName);
        FileOutputStream fos = null;
        PreparedStatement pst = null;
        DBHelper dbHelper = null;
        try{
            fos = new FileOutputStream(exportFilePath);
            /* 建立数据库连接 */
            dbHelper = new DBHelper();
            pst = getPrepareStatement(sql, dbHelper);
            /* 查询SQL获取结果 */
            ResultSet resultSet = pst.executeQuery();
            /* 将查询结果集导出到excel文件中 */
            int count = export2Execl(headers, columns, fos, resultSet);
            resultSet.close();
            logger.debug(String.format("count=%d,use time=%d", count, (System.currentTimeMillis() - startAll)));
            return SystemConfigUtil.getSystemConfig().getHostPrefix() + SystemConfig.UPLOAD_FILE_DIR + exportFileName;
        }
        catch(SQLException | ClassNotFoundException | IOException e){
            e.printStackTrace();
            throw new IOException(e.getMessage());
        }
        finally{
            close(fos, pst, dbHelper);
        }
    }

    /**
     * @Title: export2Execl
     * @Description:导出到Excel文件
     * @param headers:导出文件列标题
     * @param columns:导出数据对应库字段
     * @param fos:输出文件流
     * @param results:数据查询结果集
     * @throws SQLException
     * @throws IOException
     */
    private static int export2Execl(final String[] headers, final String[] columns, FileOutputStream fos,
            ResultSet results1) throws SQLException, IOException{
        int count = 0;
        List<Record> list = new ArrayList<Record>();
        int rowIndex = 1;
        ExportUtils exportUtils = new ExportUtils(headers);
        while(results1.next()){
            Record record = new Record();
            for(int i = 0; i < columns.length; i++){
                record.set(columns[i], results1.getObject(columns[i]));
            }
            list.add(record);
            count++;
            if(count % 5000 == 0){
                exportUtils.exportExcel(columns, list, "", rowIndex);
                rowIndex += list.size();
                list.clear();
            }
        }
        if(list.size() > 0){
            exportUtils.exportExcel(columns, list, "", rowIndex);
            list.clear();
        }
        exportUtils.write(fos);
        return count;
    }

    /**
     * @Title: getPrepareStatement
     * @Description:获取JDBC PrepareStatement(采用的是流数据接收方式，每次只从服务器接收部份数据，直到所有数据处理完毕
     *                     ，不会发生JVM OOM)
     * @param sql:查询SQL语句
     * @param dbHelper:数据库JDBC连接对象
     * @return:PrepareStatement
     * @throws SQLException
     *             PreparedStatement
     */
    private static PreparedStatement getPrepareStatement(String sql, DBHelper dbHelper) throws SQLException{
        PreparedStatement pst;
        pst = dbHelper.conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        pst.setFetchSize(Integer.MIN_VALUE);
        pst.setFetchDirection(ResultSet.FETCH_REVERSE);
        return pst;
    }

    private static void close(FileOutputStream fos, PreparedStatement pst, DBHelper dbHelper){
        if(fos != null){
            try{
                fos.close();
            }
            catch(IOException e){
                e.printStackTrace();
            }
        }
        if(pst != null){
            try{
                pst.close();
            }
            catch(SQLException e){
                e.printStackTrace();
            }
        }
        if(dbHelper != null){
            dbHelper.close();
        }
    }

    public static class DBHelper{
        public Connection conn = null;

        public DBHelper() throws SQLException, ClassNotFoundException{
            Prop prop = PropKit.use("jdbc.properties", "utf-8");
            String url = prop.get("jdbcUrl");
            String username = prop.get("user");
            String password = prop.get("password");
            String driverClass = prop.get("driverClass");
            Class.forName(driverClass);// 指定连接类型
            conn = DriverManager.getConnection(url, username, password);// 获取连接
        }

        public void close(){
            try{
                this.conn.close();
            }
            catch(SQLException e){
                e.printStackTrace();
            }
        }
    }
}
